/**
	EXEC SP_QUOTATION_REPORT 0, 0, NULL, 0, 0, '0', '8/26/2008', NULL, 'GANESH', 'SUJAN'
*/
ALTER PROC SP_QUOTATION_REPORT ( 				
				@CUSTOMER_ID	VARCHAR(1000),
				@PRODUCT_ID	VARCHAR(1000),
				@REGION		VARCHAR(50),
				@ENDUSER_ID	INT,
				@ENDCUSTOMER_ID	INT,
				@SALESPERSON	VARCHAR(500),
				@FROM_DATE	VARCHAR(10),
				@TO_DATE	VARCHAR(10),
				@END_CUSTOMER_LABEL VARCHAR(100),
				@END_USER_LABEL VARCHAR(100)
				)
AS
BEGIN
	DECLARE @RESULT_STR NVARCHAR(4000)
	SET @RESULT_STR = N'	
		SELECT C.C_NAME AS [CUSTOMER], 
			CASE WHEN ( QT.Q_ODM_ID IS NULL OR QT.Q_ODM_ID <= 0  )
				THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Customer_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ODM_ID )
		 	END AS ['+ ISNULL(@END_CUSTOMER_LABEL, '''END CUSTOMER''') +'],	
			CASE WHEN ( QT.Q_ENDCUSTOMER_ID IS NULL OR QT.Q_ENDCUSTOMER_ID <= 0  ) 
				THEN (CASE WHEN ( QT.Q_ODM_ID IS NULL OR QT.Q_ODM_ID <= 0 ) 
					THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Customer_ID )
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  QT.Q_ODM_ID )
					END) 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ENDCUSTOMER_ID)
			END AS ['+ ISNULL(@END_USER_LABEL, '''END USER''') +'],
			Q_NEW_END_CUSTOMER AS [NEW '+ ISNULL(@END_CUSTOMER_LABEL, '''END CUSTOMER''') +'],
			Q_NEW_END_USER AS [NEW '+ ISNULL(@END_USER_LABEL, '''END USER''') +'],
			Q_SALES_PERSON AS [SALES PERSON],
			QT.QUOTATION_NO AS [QUOTATION NO],
			QT.Q_QUOTE_TYPE AS [QUOTATION TYPE],
			QT.Q_BOOKING_DATE AS [BOOKING DATE],
			QT.EXPIRY_DATE AS [EXPIRY DATE],
			QLI.QLI_LINE_ITEM_NO AS [LINE ITEM NO],
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = QLI.PRODUCT_ID) AS [PRODUCT],
			QLI.QLI_REQUESTED_DATE AS [REQUESTED DOCK DATE],
			QLI.QLI_ACTUAL_QTY AS [ACTUAL QTY],
			QLI.QLI_QTY AS [REMAINING QTY],
			QLI.QLI_UNIT_COST AS [UNIT PRICE],
			(QLI.QLI_QTY*QLI.QLI_UNIT_COST) AS [EXTENDED PRICE],
			(CASE WHEN QLI.IS_APPROVED = 0 
				THEN ''APPROVED''
				ELSE ''HOLD''
			 END)  AS [LINE ITEM STATUS],
			(CASE WHEN (QT.Expiry_Date > CURRENT_TIMESTAMP)
				THEN ''''
				ELSE ''EXPIRED''
			END) AS [IS EXPIRED]
			
		FROM
		QUOTATION_TABLE QT
		INNER JOIN QUOTATION_ORDER_LINEITEM QLI	ON QT.QUOTATION_ID = QLI.QUOTATION_ID
		INNER JOIN CUSTOMER C ON C.CUSTOMER_ID = QT.CUSTOMER_ID
		WHERE 1=1 '

	IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0'
	BEGIN
		SET @RESULT_STR = @RESULT_STR + ' AND QLI.PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0'
	BEGIN
		SET @RESULT_STR = @RESULT_STR + ' AND QT.Customer_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMER_ID +'''))'
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @RESULT_STR = @RESULT_STR + ' AND C.C_REGION = '''+ @REGION+''''
	END
	IF @ENDCUSTOMER_ID IS NOT NULL AND @ENDCUSTOMER_ID > 0
	BEGIN
		SET @RESULT_STR = @RESULT_STR + ' AND CASE WHEN ( QT.Q_ODM_ID IS NULL OR QT.Q_ODM_ID <= 0  ) 
								THEN ( QT.Customer_ID )
								ELSE ( QT.Q_ODM_ID )
						 	 END = '+ CONVERT(VARCHAR,@ENDCUSTOMER_ID)
	END
	IF @SALESPERSON IS NOT NULL AND @SALESPERSON <> '0'
	BEGIN
		SET @RESULT_STR = @RESULT_STR + ' AND QT.Q_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDUSER_ID IS NOT NULL AND @ENDUSER_ID <> '0'
	BEGIN
		SET @RESULT_STR = @RESULT_STR + ' AND CASE WHEN ( QT.Q_ENDCUSTOMER_ID IS NULL OR QT.Q_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( QT.Q_ODM_ID IS NULL OR QT.Q_ODM_ID <= 0 ) 
									THEN ( QT.Customer_ID )
									ELSE ( QT.Q_ODM_ID )
									END) 
								ELSE ( QT.Q_ENDCUSTOMER_ID )
								END = '+ CONVERT(VARCHAR,@ENDUSER_ID)
	END

	IF ( @FROM_DATE IS NOT NULL AND @FROM_DATE <> '0' )
	BEGIN
		SET @RESULT_STR = @RESULT_STR +' AND QT.Q_BOOKING_DATE >= '''+ CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TO_DATE IS NOT NULL AND @TO_DATE <> '0' )
	BEGIN
		SET @RESULT_STR = @RESULT_STR +' AND QT.Q_BOOKING_DATE <= '''+ CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' +''''
	END
	
	SET @RESULT_STR = @RESULT_STR + ' ORDER BY QT.Q_BOOKING_DATE, QT.QUOTATION_NO, QLI.QLI_LINE_ITEM_NO'
	
	--PRINT @RESULT_STR
	EXEC (@RESULT_STR)

END